package com.bamatea.reportfrom.util;

import java.sql.*;

/**
 * @Auther: libingbing
 * @Date: 2020-04-27 09 :13
 * @Description:
 */
public class JDBCUtils {

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://10.10.10.32:3306/bamawechat?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&allowPublicKeyRetrieval=true&verifyServerCertificate=false&useSSL=false", "root", "Bama@123456");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static void closeConn(Connection conn) {
        try {
            if (conn != null) {
                conn.close();
                conn = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static Statement getStmt(Connection conn) {
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return stmt;
    }

    public static PreparedStatement getPStmt(Connection conn, String sql) {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return pstmt;
    }

    public static void closeStmt(Statement stmt) {
        try {
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public static ResultSet executeQuery(Statement stmt, String sql) {
        ResultSet rs = null;
        try {
            rs = stmt.executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    public static void closeRs(ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
                rs = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static ResultSet executeQuery(Connection conn, String sql) {//重载
        ResultSet rs = null;
        try {
            rs = conn.createStatement().executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    public static void main(String[] args) {
        Connection connection = getConnection();
        Statement statement = null;
        try {
            statement = connection.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
             statement.execute("alter table `ic_general_all_wc` add index DBILLDATE(`dbilldate`)");
            //statement.execute("show index from `po_order_b_wc` ");
            ResultSet resultSet = statement.executeQuery("show index from `ic_general_all_wc`");
            while (resultSet.next()){
                String key_name = resultSet.getString(3);
                String Column_name  = resultSet.getString(5);
                System.out.println(key_name+"------->"+Column_name);
            }
            ResultSet res = statement.executeQuery("explain select CGENERALBID, CGENERALHID, CINVBASID, INVCODE, PK_INVCL, INVCLASSCODE, DBILLDATE, DBIZDATE, DACCOUNTDATE, NINNUM, NOUTNUM, FBILLFLAG, DR, TS\n" +
                    " FROM ic_general_all_wc\n" +
                    " WHERE ( INVCLASSCODE = '0111' );");

            while (res.next()){
                String key = res.getString("key");
                String type = res.getString("type");
                String possible_keys = res.getString("possible_keys");
                String key_len = res.getString("key_len");
                String ref = res.getString("ref");
                String table = res.getString("table");
                System.out.println(key+"----"+key_len+"---"+ref+"----"+table);
                System.out.println(type+"-----"+possible_keys);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
